﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using PPIDataAccess.General;

namespace PPIDataAccess.Data
{
    public class DUtility_Asset_XMissionStructure_Pole
    {
        /// <summary>
        /// Insert a new Pole
        /// </summary>
        /// <param name="utilityID"></param>
        /// <param name="XMissionStructures_PoleTopTypeID"></param>
        /// <param name="XMissionStructures_PoleSpeciesID"></param>
        /// <param name="XMissionStructures_PoleTreatmentID"></param>
        /// <param name="Year"></param>
        /// <param name="XMissionStructures_PoleClassID"></param>
        /// <param name="Latitude"></param>
        /// <param name="Longitude"></param>
        /// <param name="Altitude"></param>
        /// <param name="SatelliteCount"></param>
        /// <param name="SatelliteTime"></param>
        /// <param name="AID"></param>
        /// <param name="LVisit"></param>
        /// <param name="Number"></param>
        /// <param name="Length"></param>
        /// <param name="LVisitor"></param>
        /// <param name="SolutionAvaible"></param>
        /// <returns></returns>
        public static Int32 Insert(Int32 utilityID, Int32 XMissionStructures_PoleTopTypeID, Int32 XMissionStructures_PoleSpeciesID, Int32 XMissionStructures_PoleTreatmentID, Int32 Year, Int32 XMissionStructures_PoleClassID, double Latitude, double Longitude, double Altitude, Int32 SatelliteCount, DateTime SatelliteTime, string AID, Int32 LVisit, string Number, double Length, string LVisitor, bool SolutionAvaible, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Utility_Asset_XMissionStructures_Poles_Insert", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_Visit_XMissionStructureFK", ParameterDirection.Input, false, utilityID));
                cmd.Parameters.Add(DBHelper.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, SolutionAvaible));

                #region XMissionStructures_PoleTopTypeID
                if (XMissionStructures_PoleTopTypeID == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleTopTypeFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleTopTypeFk", ParameterDirection.Input, false, XMissionStructures_PoleTopTypeID));
                }
                #endregion

                #region XMissionStructures_PoleSpeciesID
                if (XMissionStructures_PoleSpeciesID == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleSpeciesFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleSpeciesFk", ParameterDirection.Input, false, XMissionStructures_PoleSpeciesID));
                }
                #endregion

                #region XMissionStructures_PoleTreatmentID
                if (XMissionStructures_PoleTreatmentID == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleTreatmentFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleTreatmentFk", ParameterDirection.Input, false, XMissionStructures_PoleTreatmentID));
                }
                #endregion

                #region Year
                if (Year == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, Year));
                }
                #endregion

                #region XMissionStructures_PoleClassID
                if (XMissionStructures_PoleClassID == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleClassFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleClassFk", ParameterDirection.Input, false, XMissionStructures_PoleClassID));
                }
                #endregion


                #region Latitude
                if (Latitude == double.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, Latitude));
                }
                #endregion

                #region Longitude
                if (Longitude == double.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, true, DBNull.Value));

                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, Longitude));

                }
                #endregion

                #region Altitude
                if (Altitude == double.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, true, DBNull.Value));

                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, false, Altitude));

                }
                #endregion

                #region SatelliteCount

                if (SatelliteCount == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, false, SatelliteCount));
                }



                #endregion

                #region SatelliteTime
                if (SatelliteTime == DateTime.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, SatelliteTime));
                }

                #endregion

                #region AID
                if (string.IsNullOrEmpty(AID))
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@AID", 100, ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@AID", 100, ParameterDirection.Input, false, AID));
                }
                #endregion

                #region LVisit
                if (LVisit == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@LVisit", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@LVisit", ParameterDirection.Input, false, LVisit));
                }
                #endregion

                #region Number
                if (String.IsNullOrEmpty(Number))
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Number", 10, ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Number", 10, ParameterDirection.Input, false, Number));
                }
                #endregion

                #region Length
                if (Length == float.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Length", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Length", ParameterDirection.Input, false, Length));
                }
                #endregion

                #region LVisitor
                if (String.IsNullOrEmpty(LVisitor))
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@LVisitor", 100, ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@LVisitor", 100, ParameterDirection.Input, false, LVisitor));
                }
                #endregion

                cmd.Parameters.Add(DBHelper.CreateIntParameter("@Utility_Asset_XMissionStructures_PoleId", ParameterDirection.ReturnValue, false, 0));
                cmd.ExecuteNonQuery();
                tr.Commit();
                return (Int32)cmd.Parameters["@Utility_Asset_XMissionStructures_PoleId"].Value;
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
                return 0;
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }

        }

        /// <summary>
        /// Update a pole by poleId
        /// </summary>
        /// <param name="poleID"></param>
        /// <param name="utilityID"></param>
        /// <param name="XMissionStructures_PoleTopTypeID"></param>
        /// <param name="XMissionStructures_PoleSpeciesID"></param>
        /// <param name="XMissionStructures_PoleTreatmentID"></param>
        /// <param name="Year"></param>
        /// <param name="XMissionStructures_PoleClassID"></param>
        /// <param name="Latitude"></param>
        /// <param name="Longitude"></param>
        /// <param name="Altitude"></param>
        /// <param name="SatelliteCount"></param>
        /// <param name="SatelliteTime"></param>
        /// <param name="AID"></param>
        /// <param name="LVisit"></param>
        /// <param name="Number"></param>
        /// <param name="Length"></param>
        /// <param name="LVisitor"></param>
        /// <param name="SolutionAvaible"></param>
        public static void Update(Int32 poleID, Int32 utilityID, Int32 XMissionStructures_PoleTopTypeID, Int32 XMissionStructures_PoleSpeciesID, Int32 XMissionStructures_PoleTreatmentID, Int32 Year, Int32 XMissionStructures_PoleClassID, double Latitude, double Longitude, double Altitude, Int32 SatelliteCount, DateTime SatelliteTime, string AID, Int32 LVisit, string Number, double Length, string LVisitor, bool SolutionAvaible, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Utility_Asset_XMissionStructures_Poles_Update", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_Visit_XMissionStructures_PoleId", ParameterDirection.Input, false, poleID));
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_Visit_XMissionStructureFK", ParameterDirection.Input, false, utilityID));
                cmd.Parameters.Add(DBHelper.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, SolutionAvaible));

                #region XMissionStructures_PoleTopTypeID
                if (XMissionStructures_PoleTopTypeID == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleTopTypeFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleTopTypeFk", ParameterDirection.Input, false, XMissionStructures_PoleTopTypeID));
                }
                #endregion

                #region XMissionStructures_PoleSpeciesID
                if (XMissionStructures_PoleSpeciesID == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleSpeciesFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleSpeciesFk", ParameterDirection.Input, false, XMissionStructures_PoleSpeciesID));
                }
                #endregion

                #region XMissionStructures_PoleTreatmentID
                if (XMissionStructures_PoleTreatmentID == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleTreatmentFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleTreatmentFk", ParameterDirection.Input, false, XMissionStructures_PoleTreatmentID));
                }
                #endregion

                #region Year
                if (Year == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, Year));
                }
                #endregion

                #region XMissionStructures_PoleClassID
                if (XMissionStructures_PoleClassID == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleClassFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_PoleClassFk", ParameterDirection.Input, false, XMissionStructures_PoleClassID));
                }
                #endregion


                #region Latitude
                if (Latitude == double.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, Latitude));
                }
                #endregion

                #region Longitude
                if (Longitude == double.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, true, DBNull.Value));

                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, Longitude));

                }
                #endregion

                #region Altitude
                if (Altitude == double.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, true, DBNull.Value));

                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, false, Altitude));

                }
                #endregion

                #region SatelliteCount

                if (SatelliteCount == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, false, SatelliteCount));
                }



                #endregion

                #region SatelliteTime
                if (SatelliteTime == DateTime.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, SatelliteTime));
                }

                #endregion

                #region AID
                if (string.IsNullOrEmpty(AID))
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@AID", 100, ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@AID", 100, ParameterDirection.Input, false, AID));
                }
                #endregion

                #region LVisit
                if (LVisit == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@LVisit", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@LVisit", ParameterDirection.Input, false, LVisit));
                }
                #endregion

                #region Number
                if (String.IsNullOrEmpty(Number))
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Number", 10, ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Number", 10, ParameterDirection.Input, false, Number));
                }
                #endregion

                #region Length
                if (Length == float.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Length", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Length", ParameterDirection.Input, false, Length));
                }
                #endregion

                #region LVisitor
                if (String.IsNullOrEmpty(LVisitor))
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@LVisitor", 100, ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@LVisitor", 100, ParameterDirection.Input, false, LVisitor));
                }
                #endregion

                cmd.Parameters.Add(DBHelper.CreateIntParameter("@Utility_Asset_XMissionStructures_PoleId", ParameterDirection.ReturnValue, false, 0));
                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        /// <summary>
        /// Delete a pole
        /// </summary>
        /// <param name="poleID"></param>
        public static void Delete(Int32 poleID, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Utility_Asset_XMissionStructures_Poles_Delete", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, poleID));
                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }
    }
}
